#!/usr/bin/python
# -*- coding: utf-8 -*-  
"""
@Project : hello 
@file : tmp_query2.py
@Author : sheen
@time : 2025/5/16 15:38
@func :
"""
import logging
import time
import pandas as pd
from com.cn.for_cdc.common.cdc_conmons import mysql_pro_connections
from com.cn.for_cdc.common.log_helper import configure_logging
from mysql_demo import MySQLHelper

configure_logging()

df=pd.read_csv(r"F:\workspace\pythonProject\hello\com\cn\for_cdc\input_data\KVB-MISSING_PROMO_ID.csv") # 34507



loy_rdm_itm_ids = df['ROW_ID'].values.tolist()
# logging.info(tuple(loy_rdm_itm_ids))
logging.info(len(loy_rdm_itm_ids))

bu='kvb'
batch_size = 10000  # 根据数据库性能调整
results = []

db_configs = mysql_pro_connections[f'{bu}_pro_proxy']
logging.info(db_configs)
db = MySQLHelper(**db_configs)
start = time.time()

for i in range(0, len(loy_rdm_itm_ids), batch_size):
    batch = loy_rdm_itm_ids[i:i + batch_size]
    query_sql1=f'''select LOY_RDM_ITM_ID from CDC_{bu.upper()}_JOB_RW.CUSTOMER_LOY_RDM_ITM where LOY_RDM_ITM_ID in {tuple(batch) if len(batch) > 1 else f"('{batch[0]}')"}'''
    query_sql2=f'''select LOY_ACRL_ITM_ID from CDC_{bu.upper()}_JOB_RW.CUSTOMER_LOY_ACRL_ITM where LOY_ACRL_ITM_ID in {tuple(batch) if len(batch) > 1 else f"('{batch[0]}')"}'''
    query_sql3=f'''
    SELECT co.ORDER_ID FROM
    CDC_{bu.upper()}_JOB_RW.CUSTOMER_ORDER_ITEM_XM coix
    JOIN CDC_{bu.upper()}_JOB_RW.CUSTOMER_ORDER_ITEM coi ON coi.ORDER_ITEM_ID = coix.PAR_ORD_ITM_XM_ID and coi.MEMBER_ID = coix.MEMBER_ID
    JOIN CDC_{bu.upper()}_JOB_RW.CUSTOMER_ORDER co on co.ORDER_ID = coi.ORDER_ID AND coi.MEMBER_ID = co.LOY_MEMBER_ID
    WHERE coix.ORD_ITM_XM_ID in {tuple(batch) if len(batch) > 1 else f"('{batch[0]}')"} '''

    query_sql4=f'''
    SELECT coi.ORDER_ID FROM
    CDC_{bu.upper()}_JOB_RW.CUSTOMER_ORDER_ITEM_XM coix
    JOIN CDC_{bu.upper()}_JOB_RW.CUSTOMER_ORDER_ITEM coi ON coi.ORDER_ITEM_ID = coix.PAR_ORD_ITM_XM_ID and coi.MEMBER_ID = coix.MEMBER_ID    
    WHERE coix.ORD_ITM_XM_ID in {tuple(batch) if len(batch) > 1 else f"('{batch[0]}')"} '''






    # logging.info(sql)
    # 执行SQL并收集结果
    # cursor.execute(sql)
    # results.extend(cursor.fetchall())

    update_sql1=f'UPDATE CDC_{bu.upper()}_JOB_RW.CUSTOMER_LOY_RDM_ITM SET LAST_UPADTED = UTC_TIMESTAMP WHERE LOY_RDM_ITM_ID IN {tuple(batch) if len(batch) > 1 else f"('{batch[0]}')"} '
    update_sql2=f'UPDATE CDC_{bu.upper()}_JOB_RW.CUSTOMER_LOY_ACRL_ITM SET LAST_UPDATED = UTC_TIMESTAMP WHERE LOY_ACRL_ITM_ID IN {tuple(batch) if len(batch) > 1 else f"('{batch[0]}')"} '




    try:
        query_sql = query_sql2
        results = db.query(query_sql)
        # results = db.execute(update_sql2)
        logging.info(f"查询到 {len(results)} 条记录:")
        for row in results:
            logging.info(row)
    except Exception as e:
        logging.info(e)


end = time.time()
logging.info("整体耗时:{:.2f}s".format(end - start))

